<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

/**
* 
*/
class Mjc extends CI_Model
{
	
	function __construct()
	{
		# code...
	}

	public function get_cluster_id($user_id)
	{
		$this->db->select("f_get_cluster('$user_id')");
		$query = $this->db->get();
		return $query->row()->f_get_cluster;
	}

	public function get_journey_cycle($sales_type, $sales_id, $month="", $keyword="", $limit=15, $offset=0, $agent_type="", $selling_type="")
	{
		$keyword = str_replace("'", "''", $keyword);
		// Agents
		if ($sales_type == 9 ) {
			$this->db->select("tt.territory_id as t_id, aa.agent_id, tt.territory_name, jc.*");
			$this->db->join("t_mtr_agent as aa","tt.territory_id = aa.territory_id");
			// $this->db->join("t_jc_".$sales_type." as jc","jc.territory_id = tt.territory_id",'left');
			$this->db->where("aa.agent_selling_type", $selling_type);
			$this->db->where("aa.agent_type", $agent_type);
			// $this->db->where("jc.yyyymm", $month);
			if ($keyword){
				$search_1 = "(upper(aa.agent_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$search_2 = "(upper(aa.agent_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$this->db->where($search_1);
				$this->db->or_where($search_2);
			}
		}

		// Smiley
		elseif ($sales_type == 32 ) {
			$this->db->select("tt.territory_id as t_id, aa.outlet_id, tt.territory_name, jc.*");
			$this->db->join("t_mtr_outlet as aa","tt.territory_id = aa.territory_id");
			if ($keyword){
				$search_1 = "(upper(aa.outlet_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$search_2 = "(upper(aa.outlet_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$this->db->where($search_1);
				$this->db->or_where($search_2);
			}
		}
		// Outlets
		else {
			$this->db->select("tt.territory_id as t_id, aa.outlet_id, tt.territory_name, jc.*");
			$this->db->join("t_mtr_outlet as aa","tt.territory_id = aa.territory_id");
			if ($keyword){
				$search_1 = "(upper(aa.outlet_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$search_2 = "(upper(aa.outlet_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
				$this->db->where($search_1);
				$this->db->or_where($search_2);
			}
		}

		// $this->db->where("jc.yyyymm", $month );
		if( intval($month) < intval(date('Ym')) )
			{ 
				$this->db->where("jc.yyyymm", $month ); 
				$this->db->join("t_jc_".$sales_type." as jc","jc.territory_id = tt.territory_id and jc.user_id = '$sales_id' ",'left');
			}
		else {
			$this->db->join("t_jc_".$sales_type." as jc","jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' and jc.user_id = '$sales_id'",'left');
		}
		if ($selling_type == 1 OR $sales_type == 8 OR $sales_type == 32 && !$keyword) 
			{ $this->db->where("tt.user_id", $sales_id); }
		if ($selling_type == 2)
			{ $this->db->where("f_get_cluster('$sales_id')", "tt.parent_id", false); }
		if (!$keyword) 
			{ $this->db->limit($limit,$offset); }
		$this->db->where('aa.istatus', 1);
		$this->db->order_by("tt.territory_name", "asc");
		$query = $this->db->get('t_mtr_territory as tt');

		// if there is no JC record in next month, just display 
		if (!$query->row() && intval($month) >= intval(date('Ym')) && !$keyword ) 
			{ return $this->_empty_jc_on_the_next_month($sales_type, $sales_id, $agent_type, $selling_type, $month); }
		else
			{ return $query->result_array(); }
	}

	private function _empty_jc_on_the_next_month($sales_type, $sales_id, $agent_type="", $selling_type="", $month)
	{
		if ($sales_type == 9) {
			$this->db->select("tt.territory_id as t_id, aa.agent_id, tt.territory_name, aa.agent_selling_type, aa.agent_status, aa.agent_type");
		}
		else {
			$this->db->select("tt.territory_id as t_id, aa.outlet_id, tt.territory_name");
		}
		for ($i=1; $i <= 31; $i++) { 
			// $this->db->select("quote_literal(0) as quote_ident('d$i')");
			$this->db->select('null as d'.$i, false);
		}
		if ($sales_type == 9) {
			$this->db->join("t_mtr_agent as aa","tt.territory_id = aa.territory_id");
			$this->db->where("aa.agent_selling_type", $selling_type);
			$this->db->where("aa.agent_type", $agent_type);
		}
		else {
			$this->db->join("t_mtr_outlet as aa","tt.territory_id = aa.territory_id");
		}
		$this->db->where("tt.user_id", $sales_id);
		$this->db->where('aa.istatus', 1);
		$query = $this->db->get('t_mtr_territory as tt');

		return $query->result_array();
	}

	/*
	*	$user_group_id: 
	*		8  SFA   : Dapat melakukan Sell In saja ke outlet yang dikelola oleh SFA yang bersangkutan
	*		9  DS    : Dapat melakukan Sell In ke Agent yang dikelola dan juga mobile selling di lokasi mobsell yang terdapat di dalam cluster
	*		31 SGS   : DApat melakukan Mobile Selling seperti DS dan melakukan sell out di outlet-outlet yang mengikuti program SGO
	*		32 SMILE : Smile mini gallery hanya memaintain satu outlet
	*		35 SGA   : Gabungan dari SFA dan SGS. Dapat melakukan Sell In ke outlet yang dikelola oleh SGA ybs, dapat melakukan Sell Out dan Mobile Selling
	*		3x SGP   : Smartfren Ga
	*	Journey Cycle yang ditampilkan berbeda-beda berdasarkan rule yang sudah dijabarkan di controller/jc,
	*	dan untuk mempermudah pengembangan lebih lanjut maka rule penjualan yang menjadi acuan Journey Cycle 
	*	dipisahkan menjadi query yang independent
	*/
	public function jc_sales($cluster_id, $user_group_id, $sales_id, $month, $keyword, $limit, $offset, $agent_type, $selling_type, $programs)
	{
		if($user_group_id == 8 OR $user_group_id == 32) {
			return $this->_outlet_sell_in($sales_id, $user_group_id, $cluster_id, $month, $keyword, $offset, $limit);
		}
		if($user_group_id == 9) {
			if($selling_type == 1) 
				{ return $this->_agent_sell_in($sales_id, $user_group_id, $cluster_id, $agent_type, $selling_type, $month, $keyword, $offset, $limit); }
			else
				{ return $this->_mobsell($sales_id, $user_group_id, $cluster_id, $agent_type, $selling_type, $month, $keyword, $offset, $limit); }
		}
		if($user_group_id == 35) {
			if($selling_type == 1)
				{ return $this->_outlet_sell_in($sales_id, $user_group_id, $cluster_id, $month, $keyword, $offset, $limit); }
			else 
				{ return $this->_mobsell($sales_id, $user_group_id, $cluster_id, $agent_type, $selling_type, $month, $keyword, $offset, $limit); }
		}
		if($user_group_id == 36 OR $user_group_id == 31)
			{ return $this->_program_based($sales_id, $user_group_id, $cluster_id, $programs, $month, $keyword, $offset, $limit); }
	}

	private function _outlet_sell_in($sales_id, $user_group_id, $cluster_id, $month, $keyword, $offset, $limit)
	{
		$keyword = str_replace("'", "''", $keyword);
		$this->db->select('tt.territory_id as t_id, tt.territory_name, pp.outlet_id as partner_id, jc.*');
		$this->db->join('t_mtr_outlet pp', 'pp.territory_id = tt.territory_id');
		$this->db->join("t_jc_$user_group_id jc","jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' AND jc.user_id = '$sales_id'",'left');
		$this->db->where('tt.user_id', $sales_id);
		$this->db->where('tt.parent_id', $cluster_id);
		$this->db->where('pp.istatus', 1);
		if($keyword) {
			$search_1 = "(upper(tt.territory_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$search_2 = "(upper(pp.outlet_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$this->db->where($search_1);
			$this->db->or_where($search_2);
		}
		$this->db->order_by('tt.territory_name', 'asc');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result_array();
	}

	private function _outlet_sell_out($sales_id, $user_group_id, $cluster_id, $month, $keyword, $offset, $limit)
	{
		$keyword = str_replace("'", "''", $keyword);
		$this->db->select('tt.territory_id as t_id, tt.territory_name, pp.outlet_id as partner_id, jc.*');
		$this->db->join('t_mtr_outlet pp', 'pp.territory_id = tt.territory_id');
		$this->db->join("t_jc_$user_group_id jc","jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' AND jc.user_id = '$sales_id'",'left');
		$this->db->join('t_mtr_channel_program cp', 'cp.channel_id = pp.outlet_id');
		$this->db->where('tt.user_id', $sales_id);
		$this->db->where('tt.parent_id', $cluster_id);
		$this->db->where('cp.program_id', 27); // program khusus SGO (Smartfren Gadget Outlet)
		$this->db->where('pp.istatus', 1);
		$this->db->where('cp.program_id', $value);
		if($keyword) {
			$search_1 = "(upper(tt.territory_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$search_2 = "(upper(pp.outlet_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$this->db->where($search_1);
			$this->db->or_where($search_2);
		}
		$this->db->order_by('tt.territory_name', 'asc');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result_array();
	}

	private function _agent_sell_in($sales_id, $user_group_id, $cluster_id, $agent_type, $selling_type, $month, $keyword, $offset, $limit)
	{
		$keyword = str_replace("'", "''", $keyword);
		$this->db->select("tt.territory_id as t_id, tt.territory_name, pp.agent_id as partner_id, jc.*");
		$this->db->join('t_mtr_agent pp', 'pp.territory_id = tt.territory_id');
		$this->db->join("t_jc_$user_group_id jc", "jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' AND jc.user_id = '$sales_id'" ,'left');
		$this->db->where('tt.user_id', $sales_id);
		$this->db->where('tt.parent_id', $cluster_id);
		$this->db->where('pp.istatus', 1);
		$this->db->where('pp.agent_selling_type', 1);
		$this->db->where('pp.agent_type', $agent_type);
		if($keyword) {
			$search_1 = "(upper(tt.territory_name) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$search_2 = "(upper(pp.agent_id) like '%$keyword%' AND tt.user_id = '$sales_id')";
			$this->db->where($search_1);
			$this->db->or_where($search_2);
		}
		$this->db->order_by('tt.territory_name', 'asc');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result_array();
	}

	private function _mobsell($sales_id, $user_group_id, $cluster_id, $agent_type, $selling_type, $month, $keyword, $offset, $limit)
	{
		$keyword = str_replace("'", "''", $keyword);
		$this->db->select("tt.territory_id as t_id, tt.territory_name, pp.agent_id as partner_id, jc.*");
		$this->db->join('t_mtr_agent pp', 'pp.territory_id = tt.territory_id');
		$this->db->join("t_jc_$user_group_id jc", "jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' AND jc.user_id = '$sales_id'" ,'left');
		$this->db->where('tt.parent_id', $cluster_id);
		$this->db->where('pp.istatus', 1);
		$this->db->where('pp.agent_selling_type', 2);
		$this->db->where('pp.agent_type', $agent_type);
		if($keyword) {
			$this->db->like("upper(tt.territory_name)", "$keyword");
			$this->db->or_like("upper(pp.agent_id)", "$keyword");
		}
		$this->db->order_by('tt.territory_name', 'asc');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result_array();
	}

	public function _program_based($sales_id, $user_group_id, $cluster_id, $program, $month, $keyword, $offset, $limit)
	{
		
		$keyword = str_replace("'", "''", $keyword);
		$where = "( ";
		$ix = 0;
		foreach ($program as $key => $val) {
			if($val OR $val != '')
			{
				$xTbl = explode('/', $key);
				$tbl[$ix]  = $xTbl[0];
				$fld  = $xTbl[1];
				$where .= str_replace(":", ".", $fld). " = '$val' AND ";
			}
		}
		// var_dump($where);
		$where = substr_replace( $where, "", -4 );
		$where .= ' )';
		// var_dump($where);
		// die();
		// foreach ($program as $key => $val) {
		// 	$where .= str_replace("__", ".", $key). " = '$val' OR ";
		// }

		// $this->db->distinct();
		$this->db->select('tt.territory_id as t_id, tt.territory_name, oo.outlet_id as partner_id, jc.*');
		// $this->db->select('x.user_id as other_user, f_get_nik_name(x.user_id) as other_user_name');
		$this->db->join('t_mtr_outlet oo'          , 'oo.territory_id = tt.territory_id');
		$this->db->join("t_jc_$user_group_id jc"   , "jc.territory_id = tt.territory_id AND jc.yyyymm = '$month' AND jc.user_id = '$sales_id'");
		// $this->db->join("t_jc_$user_group_id x"    , "x.territory_id = tt.territory_id AND x.yyyymm = '$month' AND x.user_id != '$sales_id' " ,'left');
		// $this->db->join('t_mtr_channel_category cc', 'cc.territory_id = tt.territory_id' , 'left');
		// $this->db->join('t_mtr_lookup ll'          , 'll.lookup_value = cc.category_name', 'left');
		// Sementara ini, dua baris di bawah belum dipakai?
		$this->db->join('t_mtr_channel_program cp' , 'cp.territory_id = tt.territory_id');
		$this->db->join('t_mtr_program pp'         , 'pp.program_id = cp.program_id');
		$this->db->where('tt.parent_id', $cluster_id); 
		$this->db->where('pp.program_type_id', 5);
		$this->db->where('oo.istatus', 1);
		$this->db->where($where);
		if($keyword) {
			$search_1 = "(upper(tt.territory_name) like '%$keyword%')";
			$this->db->where($search_1);
		}
		$this->db->order_by('tt.territory_name', 'asc');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result_array();
	}

	// Ambil jumlah agent/outlet yang dimaintain oleh sales ybs *****************
	public function channels_maintained($user_group_id, $sales_id, $agent_type = '' , $selling_type = '' ) 
	{
		if ($user_group_id == 9)
		{
			// Umum, Perkantoran, Edukasi
			if ($agent_type) {
				$this->db->distinct();
				$this->db->select('agent_status, count(agent_status)');
				$this->db->join("t_mtr_territory","t_mtr_territory.territory_id = t_mtr_agent.territory_id");
				$this->db->where("t_mtr_territory.user_id", $sales_id);
				$this->db->where("t_mtr_territory.istatus", 1);
				$this->db->group_by(array("agent_status")); 
				$agent_status = $this->db->get('t_mtr_agent');
				$query[] = $agent_status->result_array();
			}
/*********************************************************************************************************************/
			// 1 = Agent; 2 = Mobsel (Lokasi)
			if ($selling_type) { 
				$this->db->distinct();
				$this->db->select('agent_selling_type, count(agent_selling_type)');
				$this->db->join("t_mtr_territory","t_mtr_territory.territory_id = t_mtr_agent.territory_id");
				$this->db->where("t_mtr_territory.user_id", $sales_id);
				$this->db->where("t_mtr_territory.istatus", 1);
				$this->db->group_by(array("agent_selling_type")); 
				$agent_selling_type = $this->db->get('t_mtr_agent');
				$query[] = $agent_selling_type->result_array();
			}
		}
		else {
			// Smartfren Ambassador
			$this->db->distinct();
			$this->db->select("count(t_mtr_territory.territory_id)");
			$this->db->join("t_mtr_territory","t_mtr_territory.territory_id = t_mtr_outlet.territory_id");
			$this->db->where("t_mtr_territory.user_id", $sales_id );
			$this->db->where('t_mtr_territory.istatus', 1);
			$query = $this->db->get("t_mtr_outlet")->result();
		}
		return $query;
	}

	/*=============================================================================================================*/
	/* Query berikut ini untuk mengambil jumlah kunjungan perhari dalam satu bulan                                 */
	/* Akan muncul di bagian bawah tabel journey cycle sebagai jumlah kunjungan perhari                            */
	/*=============================================================================================================*/
	public function daily_counts($user_group_id, $sales_id, $month)
	{
		for ($i=1; $i <=31; $i++) { 
			$this->db->select("sum(d$i) as d$i");
		}
			$this->db->where('user_id', $sales_id);
			$this->db->where('yyyymm', $month);
			$query = $this->db->get('t_jc_'.$user_group_id);
			return $query->row_array();
	}

	/*=============================================================================================================*/
	public function spv_jc($data)
	{
		$this->db->select('jc.channel_id, f_get_channel_name(jc.channel_id) as channel_name');
		$this->db->select('f_get_user(jc.sales_id) as sales_name, f_get_group_caption_by_user(jc.sales_id) as user_group');
		$this->db->select('jc.sales_id, jc.journey_type');
		$this->db->where('jc.user_id', $data[0]);
		$this->db->where('jc.d'.date('j'), 1);
		$query = $this->db->get('t_jc_'.$data[1].' jc');

		$jc['today'] 	 = $query->result();
		$jc['yesterday'] = $this->_spv_od($data, -1);
		$jc['tomorrow']  = $this->_spv_od($data, +1);
		return $jc;
	}

	private function _spv_od($data, $d)
	{
		$this->db->select('jc.channel_id, f_get_channel_name(jc.channel_id) as channel_name');
		$this->db->select('f_get_user(jc.sales_id) as sales_name, f_get_group_caption_by_user(jc.sales_id) as user_group');
		$this->db->select('jc.sales_id, jc.journey_type');
		$this->db->where('jc.user_id', $data[0]);
		$this->db->where('jc.d'.(date('j')-$d), 1);
		$query = $this->db->get('t_jc_'.$data[1].' jc');
		return $query->result();
	}

	/*=============================================================================================================*/
	public function get_spv_jc($sales_id, $date, $spv, $keyword, $jc_type, $as_type = "", $limit, $offset)
	{
		// 'Kunjungan Agent/Mobsel'
		// 'Pendampingan DS'
		// print_r($sales_id);
		$yyyymm = $date[0].$date[1];
		$cluster_id = $this->get_cluster_id($spv[0]);
		$join = "j".$spv[1].".journey_type = '$jc_type'";

		$this->db->select('f_get_channel_id_by_territory_id(tt.territory_id) as channel_id');
		$this->db->select('tt.territory_id, tt.territory_name as channel_name, tt.user_id');
		$this->db->select('f_get_user(tt.user_id) as user_name, f_get_group_caption_by_user(tt.user_id) as user_group');
		$this->db->select('f_get_agent_selling_type(f_get_channel_id_by_territory_id(tt.territory_id)) as lokasi', false);

		// Jika user_group_id sales adalah 31 (SGS:Sales Gadget Specialists)
		// maka querynya sedikit special mas bero
		if($sales_id[1] == '31') {
			$this->db->select('null as "jc8", null as "jc9"', false);
			$this->db->select('j31.d'.$date[2].' as jc31, j'.$spv[1].'.d'.$date[2].' as jc ');
			$this->db->join('t_jc_31 j31', "j31.territory_id = tt.territory_id AND j31.user_id = '$sales_id[0]' AND j31.yyyymm = '$yyyymm' AND j31.d$date[2] = 1");
		}

		/**
		 * Tampilkan Outlet/Agent
		 * querynya super special 
		 */
		else {
			$this->db->select('null as jc31', false);
			$this->db->select('j'.$spv[1].'.d'.$date[2].' as jc ');
			if($sales_id[1] == '8')
				{ 
					$this->db->select('j8.d'.$date[2].' as jc8, null as jc9', false);
					$this->db->join('t_mtr_outlet aa', 'aa.territory_id = tt.territory_id'); 
					$this->db->join('t_jc_8 j8', "j8.channel_id = f_get_channel_id_by_territory_id(tt.territory_id) AND j8.user_id = '$sales_id[0]' AND j8.yyyymm = '$yyyymm' AND j8.d$date[2] = 1" ,'left');
					$search = "(lower(aa.outlet_id) LIKE '$keyword%' OR lower(tt.territory_name) LIKE '%$keyword%')";
				}
			else 
				{ 
					$this->db->select('null as jc8, j9.d'.$date[2].' as jc9', false);
					$this->db->select('aa.agent_selling_type');
					$search = "(lower(aa.agent_id) LIKE '$keyword%' OR lower(tt.territory_name) LIKE '%$keyword%')";
					$this->db->join('t_mtr_agent aa', 'aa.territory_id = tt.territory_id'); 
					if($as_type == 2) {
						$this->db->join('t_jc_9 j9', "j9.channel_id = aa.agent_id AND j9.user_id = '$sales_id[0]' AND j9.yyyymm = '$yyyymm' AND j9.d$date[2] = 1" );
					}
					else {
						if($sales_id[0])
							{ $this->db->join('t_jc_9 j9', "j9.channel_id = f_get_channel_id_by_territory_id(tt.territory_id) AND j9.user_id = '$sales_id[0]' AND j9.yyyymm = '$yyyymm' AND j9.d$date[2] = 1" ,'left'); }
						else
							{ $this->db->join('t_jc_9 j9', "j9.channel_id = f_get_channel_id_by_territory_id(tt.territory_id) AND j9.yyyymm = '$yyyymm' AND j9.d$date[2] = 1" ,'left'); }
					}
					$this->db->where('aa.agent_selling_type', $as_type);
				}


			if($sales_id[0] && $as_type != '2')
				{ $this->db->where('tt.user_id', $sales_id[0]); }
		}

		$this->db->join("t_jc_".$spv[1]." j".$spv[1], "j".$spv[1].".channel_id = f_get_channel_id_by_territory_id(tt.territory_id) AND j".$spv[1].".yyyymm = '$yyyymm' AND j".$spv[1].".d$date[2] = 1 and $join" , "left");
		$this->db->where('tt.parent_id', $cluster_id);

		if($keyword)
		{
			$this->db->where($search);
		}
		$this->db->order_by('jc, channel_name', 'asc');
		// $this->db->group_by('channel_id, tt.territory_id, channel_name, tt.user_id, user_name, aa.agent_selling_type');
		$query = $this->db->get('t_mtr_territory tt', $limit, $offset);
		return $query->result();

	}

	/**
	 * @param  [integer] $sales_type [user_group_id]
	 * @param  [array]   $data
	 * @return [array]               [status dan messege]
	 */
	function save_jc($sales_type, $data) {
		if ($data['value'] == 'true') 
			{ 
				$nilai = 1; 
				$action = 'Set '. $data['day'];
				$messege = array(
					'status' => true, 
					"messege" => "Berhasil menyimpan Journey Cycle ".$data['user_id']." ke ".$data['channel_id']
					) ;
			}
		else 
			{
				$nilai = null; 
				$action = 'Unset '. $data['day'];
				$messege = array(
					'status' => true, 
					"messege" => "Journey Cycle ".$data['user_id']." ke ".$data['channel_id']." tanggal ".substr($data['day'], 1)." di Unset"
					) ;
			}

		$this->db->trans_begin();

			$row_exist = $this->_single_jc($sales_type, $data);
			$having_jc = $this->_having_jc_to_the_same_partmer($sales_type, $data);
			if($having_jc != 0 && $data["value"] == 'true') 
				{ return array('status' => false, "messege" => $data['channel_id']." sudah memiliki jadwal kunjungan") ; }
			else {
				if (!$row_exist) 
					{
						$new_journey = array(
							'user_id'      => $data['user_id'], 
							'territory_id' => $data['territory_id'], 
							'channel_id'   => $data['channel_id'], 
							'yyyymm'       => $data['yyyymm'], 
							$data['day'] 	 => $nilai,
							'created_on' 	 => "now()",
							'created_by' 	 => $this->session->userdata("username")
						);
						if(isset($data["sales_id"]))
							{ $new_journey['sales_id'] = $data['sales_id']; }
						
						if($data['jc_type'] ) {
							$new_journey['journey_type'] = $data['jc_type'] ;
							$new_journey['sales_id'] = $data['sales_id'] ;
						}

						$action = 'New row, Set '. $data['day'];
						$journey['created_by'] = $this->session->userdata("username");
						$this->db->insert('t_jc_'.$sales_type, $new_journey);
						$jc_id = $this->db->insert_id();
					}
				else 
					{
						$update_journey = array(
							$data['day'] => $nilai,
							'updated_on' => "now()",
							'updated_by' => $this->session->userdata("username")
						);
						// $this->db->where("territory_id", $data['territory_id']);
						// $this->db->where("yyyymm", $data['yyyymm']);
						$this->db->where('jc_id', $row_exist);
						$this->db->update('t_jc_'.$sales_type, $update_journey);
						$jc_id = $row_exist;
					}
				$this->mglobal->saveLog('journey-cycle-'.$sales_type, $action.'; sales='.$data["user_id"].'; channel='.$data['channel_id'].'; month='.$data['yyyymm'] , $jc_id);
		}

		if ($this->db->trans_status() === FALSE) { 
			$this->db->trans_rollback();
			return array('status' => false, "messege" => "Gagal menyimpan Journey Cycle ".$data['user_id']." ke ".$data['channel_id']." ") ;
		}
		else { 
			$this->db->trans_commit(); 
			return $messege;
		}
	}

	private function _single_jc($sales_type, $data) {
		$this->db->select("jc_id");
		$this->db->where('user_id', $data['user_id']);
		$this->db->where("territory_id", $data['territory_id']);
		if($data['jc_type']) {
			$this->db->where("journey_type", $data['jc_type']); 
			$this->db->where('sales_id', $data['sales_id']);
		}
		$this->db->where("yyyymm", $data['yyyymm']);
		$query = $this->db->get('t_jc_'.$sales_type)->row();

		if ($query) 
			{ return $query->jc_id; }
		else 
			{ return false; }
	}

	private function _having_jc_to_the_same_partmer($sales_type, $data)
	{
		$this->db->select("sum(".$data['day'].") as today");
		$this->db->where('user_id', $data['user_id']);
		$this->db->where("territory_id", $data['territory_id']);
		$this->db->where("yyyymm", $data['yyyymm']);
		$query = $this->db->get('t_jc_'.$sales_type)->row();

		if ($query) 
			{ return $query->today; }
		else 
			{ return false; }
	}

	public function get_holidays($month)
	{
		$this->db->like("tanggal", $month, 'after');
		$query = $this->db->get("t_mtr_holiday");
		return $query->result();
	}

	public function get_eload_jc($group_id, $user_id, $date)
	{
		$this->db->select('jc.channel_id, f_get_channel_name(jc.channel_id) as channel_name, f_get_territory(jc.channel_id) as territory_id');
		$this->db->select('COALESCE(ee.eload_number,oo.eload_number, aa.agent_mdn_evo) as eload_number', false);
		$this->db->join('t_mtr_outlet_eload ee', 'ee.territory_id  = jc.territory_id', 'left');
		$this->db->join('t_mtr_outlet oo', 'oo.territory_id = jc.territory_id' ,'left');
		$this->db->join('t_mtr_agent aa', 'aa.territory_id = jc.territory_id' ,'left');
    $this->db->join('t_mtr_territory tt', 'tt.territory_id = jc.territory_id and jc.user_id = tt.user_id');
		$this->db->where('jc.user_id', $user_id);
		$this->db->where('jc.yyyymm', $date[0].$date[1]);
		$this->db->where('jc.d'.$date[2], 1);
		$query = $this->db->get('t_jc_'.$group_id.' jc');
		return $query->result();
	}


	public function get_merchan_jc($sales_id, $date, $spv, $keyword, $jc_type, $as_type = "", $limit, $offset)
	{
		// 'Kunjungan Merchandiser'
		
		$yyyymm = $date[0].$date[1];
		$cluster_id = $this->get_cluster_id($sales_id[0]);
		// $join = "j".$spv[1].".journey_type = '$jc_type'";

		$this->db->select('outlet_id ,outlet_name, tt.territory_id');
		$this->db->select("Upper(concat_ws(', ',ot.address, ot.city)) as address", false);
		$this->db->select('j6.d'.$date[2].' as jc'); 
						  
		$this->db->join('t_mtr_territory tt', 'ot.territory_id = tt.territory_id');
		$this->db->join('t_jc_6 j6', "j6.territory_id = tt.territory_id AND j6.user_id = '$sales_id[0]' 
						AND j6.yyyymm = '$yyyymm' AND j6.d$date[2] = 1" ,'left');
		$this->db->where('tt.parent_id', $cluster_id);
		$this->db->where('ot.istatus', 1);
		$search = "(lower(ot.outlet_id) LIKE '$keyword%' OR lower(ot.outlet_name) LIKE '%$keyword%')";
		
		if ($keyword){
			
			$this->db->where($search);	
		}
		$this->db->order_by('outlet_id asc, outlet_name', 'ACS');
		// $query = $this->db->query("select
		// outlet_id
		// , outlet_name,
		// Upper(concat_ws(', ',ot.address, ot.city)) as address
		// , tt.territory_id
		// from t_mtr_outlet ot
		// join t_mtr_territory tt
		// on ot.territory_id = tt.territory_id
		// and tt.parent_id = 10 
		// and ot.istatus = 1
		// ORDER BY outlet_id asc, outlet_name ASC");
		
		$query = $this->db->get('t_mtr_outlet ot',$limit,$offset);


		return $query->result();

	}

	public function createBlankJc($user_group, $data)
	{
		$this->db->trans_begin();
		
		$this->db->insert_batch("t_jc_$user_group", $data, false );
		$this->mglobal->saveLog('journey-cycle-'.$user_group, 'Insert new partner to JC, Data= '. json_encode($data), '');
		
		if ($this->db->trans_status() === FALSE) { 
		  $this->db->trans_rollback(); 
		  return array('status' => false, 'messege' => 'Gagal membuat journey cycle');
		}
		else { 
		  $this->db->trans_commit(); 
		  return array('status' => true, 'messege' => 'Journey Cycle berhasil dibuat, silakan refresh');
		}

	}

	public function merchan_jc($sales_id)
	{
		// 'Kunjungan Merchandiser'
		$d = date('j');


		$this->db->select("concat_ws(' | ',j6.user_id, ug.user_name) as maintaine", false);
		$this->db->select("Upper(concat_ws(', ',ot.address, ot.city)) as address", false);
		
		$this->db->select(" ot.outlet_id, ot.outlet_name");
		$this->db->join('t_mtr_outlet ot', 'j6.channel_id = ot.outlet_id');
		$this->db->join('t_mtr_user ug', 'ug.user_id = j6.user_id');
		$this->db->where('j6.created_by' ,$sales_id);
		$this->db->where('j6.d'.$d, 1);

		$query = $this->db->get('t_jc_6 j6');
		$jc['today'] 	 = $query->result();
		$jc['yesterday'] = $this->merchan_jc_odd($sales_id, -1);
		$jc['tomorrow']  = $this->merchan_jc_odd($sales_id, +1);
		return $jc;

	}

	public function merchan_jc_odd($sales_id, $od)
	{
		// 'Kunjungan Merchandiser'
		$d = date('j');


		$this->db->select("concat_ws(' | ',j6.user_id, ug.user_name) as maintaine", false);
		$this->db->select("Upper(concat_ws(', ',ot.address, ot.city)) as address", false);
		
		$this->db->select(" ot.outlet_id, ot.outlet_name");
		$this->db->join('t_mtr_outlet ot', 'j6.channel_id = ot.outlet_id');
		$this->db->join('t_mtr_user ug', 'ug.user_id = j6.user_id');
		$this->db->where('j6.created_by' ,$sales_id);
		$this->db->where('j6.d'.(date('j')-$od), 1);

		$query = $this->db->get('t_jc_6 j6');
		return $query->result();
		// $jc['yesterday'] = $this->_spv_od($data, -1);
		// $jc['tomorrow']  = $this->_spv_od($data, +1);
		// return $jc;

	}

	public function channel_categories($cluster_id = null)
	{
		// $data["outlet_category_ho"] = $this->_ocHO();
		$data["outlet_category_ho"] = $this->_program(1, array(5));
		$data["program_nasional"]   = $this->_program(1, array(1,3));
		$data["program_cluster"]    = $this->_program($cluster_id, array(1,3));
		return $data;
	}
	
	private function _ocHO()
	{
		$this->db->select('lookup_value, lookup_desc');
		$qwr = $this->db->get_where('t_mtr_lookup', array('lookup_key' => 'outlet_category_ho') );
		return $qwr->result();
	}

	public function _program($cluster_id, $program_type)
	{
		$this->db->select('program_id, caption');
		$this->db->order_by('program_id', 'asc');
		$this->db->where('territory_id', $cluster_id );
		$this->db->where_in('program_type_id', $program_type);
		$qwr = $this->db->get_where('t_mtr_program');
		// echo $this->db->last_query();
		return $qwr->result();
	}
}
/* End of file mjc.php */
/* Location: ./application/models/mjc.php */

